package org.light.utils;
import java.util.Iterator;
import java.util.Set;

import org.apache.commons.lang.math.NumberUtils;
import org.light.domain.Domain;
import org.light.domain.Dropdown;
import org.light.domain.Field;
import org.light.domain.ManyToMany;
import org.light.domain.Var;
import org.light.exception.ValidateException;

public class SqlReflector {
	public static String generateTableDefinition(Domain domain) throws Exception{
		String result = "create table " + domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" (";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
	        Field f = (Field)it.next();
	        String fieldName = f.getFieldName();
	        String fieldType = f.getFieldType();
	        result += changeDomainFieldtoTableColumDefinitionToken(domain, fieldName, fieldType)+ ",";
        }
		String  ptoken = generatePrimaryKeySqlToken(domain);
		if (ptoken.length() >0 ){
			result += ptoken;
		}else {
			result = result.substring(0,result.length()-1);
		}
        result += ");";
		return result;
	}

	public static String generateInsertSqlWithQuestionMark(Domain domain) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		result += "( ";
        for (Field f : domain.getFieldsWithoutId()){
	        result += StringUtil.changeDomainFieldtoTableColum(f.getFieldName())+ ",";
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
        for (Field f : domain.getFieldsWithoutId()){
	        result += "?,";
        }
        result = result.substring(0,result.length()-1);
        result += ");";
        return result;
	}
	
	public static String generateUpdateSqlWithQuestionMark(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
        for (Field f : domain.getFieldsWithoutId()){
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (!isPrimaryKey(domain, fieldName, fieldType)) {
	        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = ? ,";
	        }
        }
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlToken(domain);
        result += ";";
        return result;
	}

	public static String generateUpdateSqlWithQuestionMarkWithDeniedFields(Domain domain, Set<Field> deniedFields) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
        for (Field f : domain.getFieldsWithoutId()){
			if (!deniedFields.contains(f)){
				String fieldName = f.getFieldName();
				String fieldType = f.getFieldType();
				if (!isPrimaryKey(domain, fieldName, fieldType)) {
					result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = ? ,";
				}
			}
		}
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlToken(domain);
        result += ";";
        return result;
	}
	
	public static String generateDeleteSqlWithQuestionMark(Domain domain) throws Exception{
		String result = "delete from " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " ";
        result += generatePrimaryWhereParamSqlToken(domain);
        result += ";";
        return result;
	}
	
	public static String generateSoftDeleteSqlWithQuestionMark(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + domain.getActive().getFieldName() + " = "+domain.getDomainDeletedStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = ? ";
        result += ";";
        return result;
	}
	
	public static String generateActivateSqlWithQuestionMark(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + domain.getActive().getFieldName() + " = "+domain.getDomainActiveStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = ? ";
        result += ";";
        return result;
	}
	
	public static String changeDomainFieldtoTableColumDefinitionToken(Domain domain, String fieldName, String fieldType){
		String result = StringUtil.changeDomainFieldtoTableColum(fieldName) + " ";
		result += lookupSqlType(fieldName,fieldType) + " ";
		if (isPrimaryKey(domain,fieldName,fieldType)){
			result += "not null auto_increment";
		} else {
			result += "null ";
		}
		return result;
	}
	
	public static boolean isPrimaryKey(Domain domain,String fieldName,String fieldType){
		boolean retVal = false;
		if ("long".equalsIgnoreCase(fieldType)||"int".equalsIgnoreCase(fieldType)||"Integer".equalsIgnoreCase(fieldType)){
			if ("id".equalsIgnoreCase(fieldName)||fieldName.equalsIgnoreCase(domain.getStandardName()+"id")||fieldName.equalsIgnoreCase(domain.getDomainId().getFieldName())){
				retVal =true;
			}
		}
		return retVal;
	}
	
	public static String generatePrimaryKeySqlToken(Domain domain){
		String result = "";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (isPrimaryKey(domain, fieldName, fieldType)){
	        	result =  "primary key (" + StringUtil.changeDomainFieldtoTableColum(fieldName) +")";
	        }
        }
        return result;
	}
	
	public static String generatePrimaryWhereParamSqlToken(Domain domain){
		String result = "";
		Field f = domain.getDomainId();
		result = "where " +  StringUtil.changeDomainFieldtoTableColum(f.getFieldName()) +" = ?";
		return result;
	}
	
	public static String lookupSqlType(String fieldName, String fieldType){
		String result = "";
		if (fieldType.equalsIgnoreCase("long")) {
			result = "BigInt";
		}
		if  (fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")) {
			result = "Integer";
		}
		if  (fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")) {
			result = "Double";
		}
		if  (fieldType.equalsIgnoreCase("BigDecimal")||fieldType.equalsIgnoreCase("decimal")) {
			result = "Decimal";
		}
		if  (fieldType.equalsIgnoreCase("boolean")) {
			result = "bool";
		}
		if  (fieldType.equalsIgnoreCase("String")) {
			if (fieldName.toLowerCase().contains("comment")||fieldName.toLowerCase().contains("description")||fieldName.toLowerCase().contains("content")){
				result = "text";
			}else {
				result = "varchar(255)";
			}
		}
		return result; 
	}
	
	public static String generateSelectAllStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +";";
        return result;
	}

	public static String generateSelectAllStatementWithDeniedFields(Domain domain, Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain, deniedFields) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +";";
        return result;
	}
	
	public static String generateSelectAllByPageStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" limit ?,?;";
        return result;
	}
	
	public static String generateCountRecordStatement(Domain domain, Var countNum) throws Exception{
		String result = "select count("+ StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + ") as "+countNum.getVarName()+" from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +";";
        return result;
	}
	
	public static String generateCountActiveRecordStatement(Domain domain, Var countNum) throws Exception{
		String result = "select count("+ StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + ") as "+countNum.getVarName()+" from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+ domain.getActive().getFeildNameAsTableColumn()+" = "+domain.getDomainActiveStr()+";";
        return result;
	}
	
	public static String generateSelectByFieldStatement(Domain domain, Field field) throws Exception{
		String result = "select "+  DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+ StringUtil.changeDomainFieldtoTableColum(field.getFieldName())+" = ?;";
        return result;
	}
	
	public static String generateSelectActiveStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" = "+domain.getDomainActiveStr()+";";
        return result;
	}

	public static String generateSelectActiveStatementWithDeniedFields(Domain domain, Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain, deniedFields) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" = "+domain.getDomainActiveStr()+";";
        return result;
	}
	
	public static String generateFindByIdStatement(Domain domain) throws Exception{
		String result = "select "+DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+" = ?;";
        return result;
	}

	public static String generateFindByIdStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+" = ?;";
        return result;
	}
	
	public static String generateFindByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = ?;";
        return result;
	}

	public static String generateFindByNameStatementWithDeniedFields(Domain domain, Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain, deniedFields)+ " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = ?;";
        return result;
	}

	public static String generateFindUserShadowStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateUserShadowTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = ?;";
        return result;
	}

	public static String generateSearchIndexedNamesByShortNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like CONCAT(?,'%');";
        return result;
	}
	
	public static String generateSearchByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like ?";
        return result;
	}
	
	public static String generateSearchByDescriptionStatement(Domain domain, Field description) throws Exception{
		String result = "select " +DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(description.getFieldName())+" like %?%;";
        return result;
	}
	
	public static String generateInsertSqlWithValue(Domain domain) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFields().iterator();
		
		result += "( ";
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 	        result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ ",";
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
		Iterator it2 = domain.getFields().iterator();
        while (it2.hasNext()) {	
        	Field f = (Field)it2.next();
 	        String fieldValue = f.getFieldValue();
			String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
			if ((fieldType.equalsIgnoreCase("long")&&fieldValue.endsWith("L"))||(fieldType.equalsIgnoreCase("float")&&fieldValue.endsWith("F"))) fieldValue = fieldValue.substring(0,fieldValue.length()-1);
 	        try {
	 	        if (!StringUtil.isBlank(fieldValue)&&NumberUtils.isNumber(fieldValue)){
	 	        	if (Double.valueOf(fieldValue) - Math.round(Double.valueOf(fieldValue)) < 0.00005){
	 	        		fieldValue = "" + Math.round(Double.valueOf(fieldValue));
	 	        		System.out.println("JerryDebug:roundtoint:"+fieldValue);
	 	        	}
	 	        }
 	        } catch (Exception e){
 	        	e.printStackTrace();
 	        	throw new ValidateException("数据项"+fieldName+"类型定义错误！");
 	        }
 	       if (!StringUtil.isBlank(fieldType)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")||fieldType.equalsIgnoreCase("boolean"))) {
 	    	   if (StringUtil.isBlank(fieldValue)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double"))) result += "0,";
 	    	   else result +=  fieldValue + ",";
	        } else {
	        	result += "'"+ fieldValue + "',";
	        }
        }
        result = result.substring(0,result.length()-1);
        result += ");";
        return result;
	}
	
	public static String generateInsertLinkTwoSql(Domain master,Domain slave) throws Exception{
		String result = "insert into " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
		result += "( ";
		result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
		result += ",";
		result += StringUtil.changeDomainFieldtoTableColum(StringUtil.lowerFirst(slave.getAlias())+"Id");
        result += ") values (?,?)";
        return result;
	}
	
	public static String generateSelectActiveUsingMasterIdStatement(Domain master,Domain slave) throws Exception{
		String result = "select distinct "+ DomainTokenUtil.generateTableCommaFieldsWithTablePrefix(slave) + " from "+ TableStringUtil.domainNametoTableNameWithDbPrefix(slave)+ ","+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+" where " + TableStringUtil.domainNametoTableNameWithDbPrefix(slave) +"."+slave.getDomainId().getFeildNameAsTableColumn()+" = "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+"."+StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id")+
						" and "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +"."+StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id") +" = ? and "+TableStringUtil.domainNametoTableNameWithDbPrefix(slave) + "." + slave.getActive().getFeildNameAsTableColumn() + " = "+slave.getDomainActiveStr();
        return result;
	}
	
	public static String generateDeleteLinkTwoSql(Domain master,Domain slave) throws Exception{
		String result = "delete from " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
		result += " where ";
		result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
		result += " = ? and ";
		result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id");
        result += " = ?";
        return result;
	}
	
	public static String generateLinkTableDefinition(Domain master, Domain slave) throws Exception{
		String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getStandardName()) +" (";
		result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldType()) + " not null, ";
		result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldType()) + " not null ";
        result += ");";
		return result;
	}
	
	public static String lookupSqlType(String fieldType){
		String result = "";
		if (fieldType.equalsIgnoreCase("long")) {
			result = "BigInt";
		}
		if  (fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")) {
			result = "Integer";
		}
		if  (fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")) {
			result = "Double";
		}
		if  (fieldType.equalsIgnoreCase("BigDecimal")||fieldType.equalsIgnoreCase("decimal")) {
			result = "Decimal";
		}
		if  (fieldType.equalsIgnoreCase("boolean")) {
			result = "bool";
		}
		if  (fieldType.equalsIgnoreCase("String")) {
			result = "varchar(255)";
		}
		return result; 
	}
	
	public static String generateDropLinkTableSql(Domain master, Domain slave) throws Exception{
		String result = "drop table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getStandardName()) +";\n";
		return result;
	}
	
	public static String generateLinkTableDefinition(Domain master, Domain slave, boolean commentOn, String dbType) throws Exception{
		if (commentOn == true) {
			if (StringUtil.isBlank(dbType)||"mariadb".equalsIgnoreCase(dbType)||"mysql".equalsIgnoreCase(dbType)){
				ManyToMany mtm = new ManyToMany(master,slave,"","");
				if (StringUtil.isBlank(slave.getAlias())){
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null comment '"+master.getText()+"', ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null comment '"+slave.getText()+"'";
					result += ")";
					result += " comment '" +mtm.getText() +"' ";
					result += ";";
					return result;
				} else {
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null comment '"+master.getText()+"', ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null comment '"+slave.getText()+"'";
					result += ")";
					result += " comment '" +mtm.getText() +"' ";
					result += ";";
					return result;
				}
			} else if ("postgresql".equalsIgnoreCase(dbType)||"pgsql".equalsIgnoreCase(dbType)){
				ManyToMany mtm = new ManyToMany(master,slave,"","");
				if (StringUtil.isBlank(slave.getAlias())){
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null , ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
					result += ");\n";
					
					result += " comment on table "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" is  '"+mtm.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName())+"."+StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" is '"+master.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName())+"."+StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" is '"+slave.getText()+"';\n";
					result += "\n";
					return result;
				} else {
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null , ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
					result += ");\n";
					
					result += " comment on table "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" is  '"+mtm.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias()))+"."+StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" is '"+master.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias()))+"."+StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" is '"+slave.getText()+"';\n";
					result += "\n";
					return result;
				}
			}else if ("oracle".equalsIgnoreCase(dbType)){
				ManyToMany mtm = new ManyToMany(master,slave,"","");
				if (StringUtil.isBlank(slave.getAlias())){
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null , ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
					result += ");\n";
					
					result += " comment on table "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" is  '"+mtm.getText()+"';";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName())+"."+StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" is '"+master.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName())+"."+StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" is '"+slave.getText()+"';\n";
					result += "\n";
					return result;
				} else {
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null , ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
					result += ");\n";
					
					result += " comment on table "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" is  '"+mtm.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias()))+"."+StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" is '"+master.getText()+"';\n";
					result += " comment  on  column  "+master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias()))+"."+StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" is '"+slave.getText()+"';\n";
					result += "\n";
					return result;
				}
			} else {
				if (StringUtil.isBlank(slave.getAlias())){
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null, ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
			        result += ");";
					return result;
				} else {
					String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" (";
					result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null, ";
					result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
			        result += ");";
					return result;
				}	
			}
		}else {
			if (StringUtil.isBlank(slave.getAlias())){
				String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+slave.getCapFirstDomainName()) +" (";
				result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null, ";
				result += StringUtil.changeDomainFieldtoTableColum(slave.getStandardName()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
		        result += ");";
				return result;
			} else {
				String result = "create table " + master.getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+StringUtil.capFirst(slave.getAlias())) +" (";
				result += StringUtil.changeDomainFieldtoTableColum(master.getStandardName()+"Id") +" "+lookupSqlType(master.getDomainId().getFieldName(),master.getDomainId().getFieldType(),master.getDomainId().getLengthStr()) + " not null, ";
				result += StringUtil.changeDomainFieldtoTableColum(slave.getAlias()+"Id") +" "+lookupSqlType(slave.getDomainId().getFieldName(),slave.getDomainId().getFieldType(),slave.getDomainId().getLengthStr()) + " not null ";
		        result += ");";
				return result;
			}	
		}
	}
	
	public static String generateMtmInsertSqlWithValues(ManyToMany mtm) throws Exception{
		if (StringUtil.isBlank(mtm.getValues())) return "";
		else {
		String [] slaveValues = mtm.getValues().split(",");
			String result = "";
			for (String slaveValue:slaveValues) {
			    result = result + "insert into " +  mtm.getMaster().getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+mtm.getSlaveAlias()) +" ";
				result = result + "("+StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+"Id")+" , "+StringUtil.changeDomainFieldtoTableColum(mtm.getSlaveAlias()+"Id")+") values ( ";		
		        if (TypeUtil.isNumeric(mtm.getMaster().getDomainId().getFieldRawType())){
		        	result = result + mtm.getMaster().getDomainId().getFieldValue() + "," ;
		        }else {
		        	result = result +"'"+ mtm.getMaster().getDomainId().getFieldValue() + "',"; 
		        }
		        if (TypeUtil.isNumeric(mtm.getSlave().getDomainId().getFieldRawType())){
		        	result = result + slaveValue ;
		        }else {
		        	result = result +"'"+ slaveValue + "'"; 
		        }
		        result += ");\n";
			}
			return result;
		}
	}
	
	public static String generateTableDefinitionWithComment(Domain domain) throws Exception{
		String result = "create table " + domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" (";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
	        Field f = (Field)it.next();
	        String fieldName = f.getFieldName();
	        String fieldType = f.getClassType().getTypeName();
	        String fieldLengthStr = f.getLengthStr();
	        String fieldComment = f.getText();
	        result += changeDomainFieldtoTableColumDefinitionTokenWithComment(domain, fieldName, fieldType,fieldLengthStr,fieldComment)+ ",";
        }
		String  ptoken = generatePrimaryKeySqlToken(domain);
		if (ptoken.length() >0 ){
			result += ptoken;
		}else {
			result = result.substring(0,result.length()-1);
		}
        result += ") comment '"+domain.getText()+"';";
		return result;
	}
	
	public static String changeDomainFieldtoTableColumDefinitionTokenWithComment(Domain domain, String fieldName, String fieldType, String fieldLengthStr, String fieldComment){
		String result = StringUtil.changeDomainFieldtoTableColum(fieldName) + " ";
		result += lookupSqlType(fieldName,fieldType,fieldLengthStr) + " ";
		if (isPrimaryKey(domain,fieldName,fieldType)){
			result += "not null auto_increment ";
		}else if (isDoaminIdOrDomainNameOrActiveField(domain,fieldName,fieldType)){
			result += "not null ";
		}else {
			result += "null ";
		}
		result += " comment '"+fieldComment+"'";
		return result;
	}
	
	public static String lookupSqlType(String fieldName, String fieldType,String fieldLengthStr){
		String result = "";
		if (fieldType.equalsIgnoreCase("long")) {
			result = "BigInt";
		}
		if  (fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")) {
			result = "Integer";
		}
		if  (fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")) {
			result = "Double";
		}
		if  (fieldType.equalsIgnoreCase("BigDecimal")||fieldType.equalsIgnoreCase("decimal")) {
			result = "Decimal";
		}
		if  (fieldType.equalsIgnoreCase("boolean")) {
			result = "bool";
		}
		if  (fieldType.equalsIgnoreCase("image")||fieldType.equalsIgnoreCase("byte []")) {
			result = "longblob";
		}
		if  (fieldType.equalsIgnoreCase("datetime")||fieldType.equalsIgnoreCase("date")) {
			result = "datetime";
		}
		if  (fieldType.equalsIgnoreCase("String")) {
			if (fieldName.toLowerCase().contains("comment")||fieldName.toLowerCase().contains("description")||fieldName.toLowerCase().contains("content")){
				result = "text";
			}else if (!StringUtil.isBlank(fieldLengthStr)){
				result = "varchar("+fieldLengthStr+")";
			}else {
				result = "varchar(255)";
			}
		}
		return result; 
	}
	
	public static boolean isDoaminIdOrDomainNameOrActiveField(Domain domain,String fieldName,String fieldType){
		if (domain==null||domain.isLegacy()) return false;
		if ((fieldType.equalsIgnoreCase("boolean")&&fieldName.equals(domain.getActive().getFieldName()))||(fieldType.equalsIgnoreCase("string")&&fieldName.equals(domain.getDomainName().getFieldName()))){
			return true;
		}
		return false;
	}
	
	public static String generateUpdateSqlWithValue(Domain domain) throws Exception{
		String result = "update " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFieldsWithoutId().iterator();		
		result += " set  ";
        while (it.hasNext()) {
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 			String fieldValue = f.getFieldValue();
 	        if (f instanceof Dropdown) result += StringUtil.changeDomainFieldtoTableColum(((Dropdown)f).getAliasName());
 	        else result += StringUtil.changeDomainFieldtoTableColum(fieldName);
 	        result += " = "; 
 	        if (f instanceof Dropdown && StringUtil.isBlank(fieldValue)) {
 	        	result = result + " null " ;
 	        }
 	        else if (!StringUtil.isBlank(fieldType)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")||fieldType.equalsIgnoreCase("boolean"))) {
 	    	   if (StringUtil.isBlank(fieldValue)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double"))) result += "0";
 	    	   else result = result + fieldValue ;
 	        } else {
 	        	result = result + "'"+ fieldValue + "'";
 	        }
 			result +=",";
        }

        result = result.substring(0,result.length()-1);
        
		result = result + " where " +domain.getDomainId().getTableColumnName()+ " = ";
		String fieldType = domain.getDomainId().getFieldType();
		String fieldValue = domain.getDomainId().getFieldValue();
		if (!StringUtil.isBlank(fieldType)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")||fieldType.equalsIgnoreCase("boolean"))) {
	    	   if (StringUtil.isBlank(fieldValue)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double"))) result += "0,";
	    	   else result +=  fieldValue ;
	        } else {
	        	result += "'"+ fieldValue + "'";
	        }
				
        result += ";";
        return result;
	}
	
	public static String generateDeleteSqlWithValue(Domain domain) throws Exception{
		String result = "delete from " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where ";
		result += domain.getDomainId().getTableColumnName()+ " = ";
		String fieldType = domain.getDomainId().getFieldType();
		String fieldValue = domain.getDomainId().getFieldValue();
		if (!StringUtil.isBlank(fieldType)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")||fieldType.equalsIgnoreCase("boolean"))) {
	    	   if (StringUtil.isBlank(fieldValue)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double"))) result += "0,";
	    	   else result +=  fieldValue ;
	        } else {
	        	result += "'"+ fieldValue + "'";
	        }
        result += ";";
        return result;
	}

	public static String generateMtmDeleteSqlWithValues(ManyToMany mtm) throws Exception{
		if (StringUtil.isBlank(mtm.getValues())||mtm.getSlave().isLegacy()) return "";
		else {
		String [] slaveValues = mtm.getValues().split(",");
			String result = "";
				for (String slaveValue:slaveValues) {
				    result = result + "delete from " +  mtm.getMaster().getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+StringUtil.capFirst(mtm.getSlaveAlias())) +" ";
					result = result + " where  "+StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+"Id")+" = ";
			        if (TypeUtil.isNumeric(mtm.getMaster().getDomainId().getFieldRawType())){
			        	result = result + mtm.getMaster().getDomainId().getFieldValue() + " " ;
			        }else {
			        	result = result +"'"+ mtm.getMaster().getDomainId().getFieldValue() + "',"; 
			        }
					result += " and " +StringUtil.changeDomainFieldtoTableColum(mtm.getSlaveAlias()+"Id")+" = ";	
			        if (TypeUtil.isNumeric(mtm.getSlave().getDomainId().getFieldRawType())){
			        	result = result + slaveValue ;
			        }else {
			        	result = result +"'"+ slaveValue + "'"; 
			        }
			        result += ";\n";
				} 
			return result;
		}
	}
	
	public static String generateMariaDBDropTableStatement(Domain domain) throws Exception{
		String result = "DROP TABLE  if exists "+domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain)+";";
		return result;
	}
}
